import urlparse
import threading
import traceback
import sys
import time
import json
import datetime
import sqlite3

import os
from os import curdir, sep
from BaseHTTPServer import BaseHTTPRequestHandler, HTTPServer
from FileLoader import FileLoader
import cStringIO

connection=None
cursor = None

class response(object):pass

class MoneyTrackerServer(BaseHTTPRequestHandler):

    def do_POST(self):
        return self.do_GET()
    
    def do_GET(self):
        ret = {"status":"OK", "response":""}
        try:
            parsed_path = urlparse.urlparse(self.path)
            query = urlparse.parse_qs(parsed_path[4])
#            log.info('received request: %s' % self.path)
            cmd = parsed_path[2].lower()[1:]
            if cmd.startswith("command/upload_file"):
                self.flatten(query)
                self.upload_file(query)
#                self.get_file('upload.html')
            elif cmd.startswith("command/"):
                self.execute(cmd.replace("command/", ""),query)
            else:
                self.get_file(cmd)
#            log.info('Got Command %s[%s]'%(cmd,query))
        except KeyboardInterrupt:
            raise KeyboardInterrupt()
            return
        except:
            ret["status"]="Error"
            ret["response"]=traceback.format_exc()
            print traceback.format_exc()

    def execute(self, command, query):
        self.send_response(200)
        self.send_header("Content-type", "application/json")
        self.end_headers()
        ret = ""
        if command in dir(self.__class__):
            method = getattr(self, command)
            self.flatten(query)
            ret = method(query) 
        self.wfile.write(json.dumps(ret))
    
    def flatten(self, query):
        for name in query:
            query[name] = query[name][0]
            
            
    def execute_select(self,name,sql,query = None):
        if query and query.get("flex"):
            sql = "%s order by %s %s " % (sql, query["sortname"], query["sortorder"])
            res = cursor.execute(sql,query)
            page = int(query["page"])
            recors_per_page = int(query["rp"])
            ret = {"page" : page} 
            rows = []
            for r in res:
                row = {"id": r[0], "cell" : r}
                rows.append(row)
            ret["rows"] = rows[(page-1) * recors_per_page : (page*recors_per_page)-1]
            ret["total"] = len(rows)
            return ret
        else: 
            if query.get("sortname"):
                sql = "%s order by %s" % (sql, query["sortname"])
            res = cursor.execute(sql,query)
            ret = [] 
            cols = [(i,c[0]) for i, c in enumerate(res.description)]
            for r in res:
                row = {}
                for i, c in cols:
                    row[c] = str(r[i])
                ret.append(row)
            return {name:ret}

    def upload_file(self,query):
        import cgi
        ctype, pdict = cgi.parse_header(self.headers.getheader('content-type'))
        query = cgi.parse_multipart(self.rfile, pdict)
        fileContent = query.get('qbo-file')[0]
        fl = FileLoader()
        fl.load_stream(cStringIO.StringIO(fileContent))
        self.send_response(301)
        self.send_header("Location", "/index.html")
        self.end_headers()                


    def get_account_list(self, query):
        return self.execute_select("accounts", "select id, nick, name, type from accounts", query)

    def get_account(self, query):
        return self.execute_select("account", "select id, nick, name, type from accounts where id=:id",query)
    
    def get_category_list(self, query):
        query.setdefault("sortname","c.name")
        return self.execute_select("categories", "select c.id, c.name, t.name type from categories c, categories_types t where c.type=t.id ", query)
    
    def get_vendor_list(self, query):
        query.setdefault("sortname","name")
        return self.execute_select("vendors", "select v.id, v.name from vendors v", query)
    
    def get_vendor (self, query):
        return self.execute_select("vendor", "select v.id, v.name from vendors v where id=:id",query)

    def get_rule_list(self, query):
        return self.execute_select("rules", "select r.id, r.name rule_name, r.amt, v.id vendor_id, v.name, c.id category_id, c.name, r.account from rules r, vendors v, categories c where r.vendor=v.id and r.category=c.id", query)
    
    def get_rule(self, query):
        if query.get("id"):
            return self.execute_select("rule", "select r.id, r.name, r.amt, r.vendor, r.category from rules r where r.id=:id", query)
        elif query.get("name"):
            return self.execute_select("rule", "select r.id, r.name, r.amt, r.vendor, r.category from rules r where r.name=:name", query)
    
    def get_transfer_list(self, query):
        return self.execute_select("transfers", "select  t.id, acc_from.nick nick_from, acc_to.nick nick_to, tr_from.amt, date(tr_from.posted) posted_from, date(tr_to.posted) posted_to from transfers t, transactions tr_from, transactions tr_to , accounts acc_from, accounts acc_to where t.from_transaction_id=tr_from.id and t.to_transaction_id=tr_to.id and tr_from.account_id=acc_from.id and tr_to.account_id=acc_to.id", query)
    
    def get_transfer(self, query):
        return self.execute_select("transfer", "select r.id, r.name, r.amt, r.vendor, r.category from rules r where r.id=:id" , query)
    
    
    def save_account(self, query):
        cursor.execute("update accounts set nick=:nick where id=:id" , query)
        connection.commit()
        return 1

    def save_category(self, query):
        if query['id'] != '-1':
            cursor.execute("update categories set name=:name , type=:type where id=:id" , query)
            connection.commit()
            return {"id": query['id']}
        else:
            cursor.execute("insert into categories (name,type) values(:name,:type)",query)
            connection.commit()
            id = list(cursor.execute("select seq from sqlite_sequence where name='categories'"))[0][0]
        return {"id":id}

    def save_transaction(self, query):
        if 'name' in query:
            if query.get('id'):
                cursor.execute("update transactions set name=:name, posted=:date,amt=:amt, category=:category,vendor=:vendor where id=:id",query)
            else:
                cursor.execute("insert into transactions (name,posted,amt,category,vendor, account_id) values(:name,:date,:amt,:category,:vendor,:acct)",query)
                query['id'] = -1 
            connection.commit()
            return {"id": query['id']}
        elif 'category' in query:
            cursor.execute("update transactions set category=:category where id=:id",query)
            connection.commit()
            if query.get('rule_name'):
                self.create_rule_from_transaction(query['id'], query['rule_name'])
        elif 'vendor' in query:
            cursor.execute("update transactions set vendor=:vendor where id=:id",query)
            connection.commit()
        return 1
    
    def save_vendor(self, query):
        if query['id'] != '-1':
            cursor.execute("update vendors set name='%s'  where id=%s" % (query['name'], query['id']))
            connection.commit()
            return {"id": query['id']}
        else:
            cursor.execute("insert into vendors (name) values('%s')" % (query['name']))
            connection.commit()
            id = list(cursor.execute("select seq from sqlite_sequence where name='vendors'"))[0][0]
        return {"id":id}

    def save_rule(self, query):
        if not query.get('amt'):    query['amt'] = ''
         
        if query['id'] != '-1':
            cursor.execute("update rules set name=:name , amt=:amt, vendor=:vendor, category=:category where id=:id",query)
            connection.commit()
            return {"id": query['id']}
        else:
            cursor.execute("insert into rules (name, amt, vendor, category) values(:name,:amt,:vendor,:category)", query)
            connection.commit()
            id = list(cursor.execute("select seq from sqlite_sequence where name='vendors'"))[0][0]
        return {"id":id}
    
    def create_rule_from_transaction(self, transaction_id, rule_name):
        cursor.execute("select category,vendor from transactions tr  where id=:id", {'id':transaction_id})
        res = cursor.fetchone()
        cursor.execute("insert into rules (name, vendor, category) values(:name,:vendor,:category)", {'category':res[0], 'vendor': res[1],'name':rule_name})
        connection.commit()
        self.run_rules({})

    def delete_rule(self,query): 
        cursor.execute("delete from rules where id=:id",query)
        connection.commit()
        return {}

    
    def get_transaction_list(self, query):
        sql = "select  tr.id, account_id, fitid, date(posted), tr.name, amt, tr.type, ven.name, cat.name,transfer_from.id, transfer_to.id, ven.id, cat.id  from transactions tr LEFT JOIN  vendors ven ON tr.vendor= ven.id LEFT JOIN  categories cat ON tr.category = cat.id LEFT JOIN  transfers  transfer_from ON  tr.id=transfer_from.from_transaction_id LEFT JOIN  transfers transfer_to ON tr.id=transfer_to.to_transaction_id where account_id=:account"
        if query.get("uncat") == 'true':
            sql = sql + " and (tr.vendor is null or  tr.category is null)"
        ret = self.execute_select("transactions",sql,query)
        for r in ret['rows']:
            r['cell'] = list(r['cell'])
            if r['cell'][9]:
                r['cell'][7] = 'Transfer'
        return ret
                
    def get_transaction(self, query):
        return self.execute_select("transaction", "select  tr.id tr_id, account_id, fitid, date(posted) dt, tr.name tr_name, amt, tr.type, ven.name, cat.name,transfer_from.id, transfer_to.id, ven.id ven_id, cat.id cat_id from transactions tr LEFT JOIN  vendors ven ON tr.vendor= ven.id LEFT JOIN  categories cat ON tr.category = cat.id LEFT JOIN  transfers  transfer_from ON  tr.id=transfer_from.from_transaction_id LEFT JOIN  transfers transfer_to ON tr.id=transfer_to.to_transaction_id where tr.id=:id",query)
                
    def get_category_type_list(self, query):
        return self.execute_select("category_types", "select id, name from categories_types", query)


    def get_file(self, filename = None):
        filename = filename or "index.html"
        mode = 'r'
        self.send_response(200)
        if filename.endswith(".html") :
            self.send_header('Content-type',    'text/html')
        if filename.endswith(".css") :
            self.send_header('Content-type',    'text/css')
        if filename.endswith(".js") :
            self.send_header('Content-type',    'text/javascript')
        if filename.endswith(".gif") or filename.endswith(".png")  :
            self.send_header('Content-type',    'image/%s'%filename.split('.')[1])
            mode = 'rb'
        self.end_headers()
        f = open(os.getcwd() + sep + filename, mode)
        self.wfile.write(f.read())
        f.close()

    def find_transfer(self,query):
        query['amt'] = float(query['amt'][1:]) * -1.00 #reverse
        r = self.execute_select("transactions","select id,date(posted) as posted from transactions where account_id=:account_id and amt=:amt and posted>=:posted",query)
        return r
    
    def save_transfer(self,query):
        cursor.execute("insert into transfers (from_transaction_id, to_transaction_id) values(%d,%d)" % (int(query['from_transaction_id']), int(query['to_transaction_id'])))
        connection.commit()

    def delete_transfer(self,query): 
        cursor.execute("delete from transfers where id=:id",query)
        connection.commit()
        return {}

        
    def run_rules(self,query):
        rules = self.get_rule_list(query)
        for rule in rules['rules']:
            rule['rule_search'] = "%" + rule['rule_name'].replace("*", "%") + "%" 
            cursor.execute("update transactions set category=:category_id,vendor=:vendor_id where name like :rule_search",rule)
        return {}

    
    def shutdown(self):
        self.server.shutdown()            

    def _start_custom_run(self, params):
        pass

    def _populate_progress(self):
        for id, manager in custom_runs.iteritems():
            custom_runs_status[id]["progress"] = manager.progress
        for run in custom_runs_status.values():
            run['eta'] = run['start_time'] + (time.time()-run['start_time'])/run['progress']
            
            

def main():
    global connection, cursor
    try:
        server = HTTPServer(('', 8087), MoneyTrackerServer)
        print('started httpserver...')
        connection = sqlite3.connect("../data/sb")
        cursor = connection.cursor()
        
        server.serve_forever()
    except KeyboardInterrupt:
        print '^C received, shutting down server'
        server.socket.close()

if __name__ == '__main__':
    main()

